package com.eorchis.module.classplanapply.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.OrderType;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.classplanapply.bean.ClassPlanApplyQueryBean;
import com.eorchis.module.classplanapply.bean.ClassPlanSignUserBean;
import com.eorchis.module.classplanapply.dao.IClassPlanApplyDao;
import com.eorchis.module.classplanapply.domain.ClassPlanApply;
import com.eorchis.module.classplanapply.ui.commond.ClassPlanApplyQueryCommond;
import com.eorchis.module.classplanapply.ui.commond.ClassPlanApplyValidCommond;
import com.eorchis.module.classuser.domain.ClassUser;
import com.eorchis.module.trainingclass.dao.ITrainingClassDao;
import com.eorchis.module.trainingclass.ui.commond.TrainingClassQueryCommond;
import com.eorchis.module.user.domain.User;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: ClassPlanApplyDaoImpl<br>
 * Description: 班级计划报名数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author SHQ
 * @createDate 2016-01-05
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.classplanapply.dao.impl.ClassPlanApplyDaoImpl")
public class ClassPlanApplyDaoImpl
					extends HibernateAbstractBaseDao
					implements IClassPlanApplyDao {
	
	@Autowired
	@Qualifier("com.eorchis.module.trainingclass.dao.impl.TrainingClassDaoImpl")
	private ITrainingClassDao trainingClassDao;
	public Class<? extends IBaseEntity> entityClass() {
		return ClassPlanApply.class;
	}
	
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		ClassPlanApplyQueryCommond classPlanApplyQueryCommond = (ClassPlanApplyQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM ClassPlanApply t,Department d where t.deptId=d.deptID  and ");
		defaultQueryConditionBuilder.addCondition("t.planApplyId", CompareType.IN, classPlanApplyQueryCommond.getSearchPlanApplyIds());
		defaultQueryConditionBuilder.addCondition("t.planApplyId", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchPlanApplyId());
		defaultQueryConditionBuilder.addCondition("t.deptId", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchDeptId());
		defaultQueryConditionBuilder.addCondition("t.deptCode", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchDeptCode());
		defaultQueryConditionBuilder.addCondition("t.deptName", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchDeptName());
		defaultQueryConditionBuilder.addCondition("t.planCount", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchPlanCount());
		defaultQueryConditionBuilder.addCondition("t.asignCount", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchAsignCount());
		defaultQueryConditionBuilder.addCondition("t.classId", CompareType.EQUAL,classPlanApplyQueryCommond.getSearchClassId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
		//
		List<SortInfoBean> sortInfo = classPlanApplyQueryCommond.getSortInfo();
		if(sortInfo==null||sortInfo.size()==0){
			defaultQueryConditionBuilder.addSort("d.orderNum", OrderType.ASC);
		}
	}
	
	public void deleteByClassId(List<String> classId){
		if(PropertyUtil.objectNotEmpty(classId)){
			String hql = "delete from ClassPlanApply t where t.classId in (:classIds)";
			Map<String, Object> paramMap = new HashMap<String, Object>();
			paramMap.put("classIds", classId.toArray(new String[0]));
			executeUpdate(QueryStringType.HQL, hql, paramMap);
		}
	}
	
	/**
	 * @category 根据班级查询条件获取计划报名列表
	 * @param queryCommond
	 * @return
	 * @throws Exception 
	 */
	public List<ClassPlanSignUserBean> findClassPlanSignList(TrainingClassQueryCommond queryCommond) throws Exception{
		
		StringBuffer hql = new StringBuffer(
				"select t.classId as classId,"
				+ " t.className as className,"
				+ " t.classNum as classCode,"
				+ " t.ministrieID as ministrieId,"
				+ " t.ministrieName as ministrieName,"
				+ " cp.deptId as signDeptId,"
				+ " cp.deptCode as signDeptCode,"
				+ " cp.deptName as signDeptName,"
				+ " cp.planCount as planCount,"
				+ " cp.cityPlanCount as cityPlanCount,"
				+ " cp.countyPlanCount as countyPlanCount,"
				+ " cp.asignCityCount as asignCityCount,"
				+ " cp.asignCountyCount as asignCountyCount,"
				+ " cp.asignCount as assignCount"
				+ " FROM ClassPlanApply cp,TrainingClass t left join t.classType ct left join t.applyWay aw where cp.classId=t.classId and t.activeState=1 ");
		Object[] queryObject = trainingClassDao.buildQueryObject(queryCommond, hql);
		Map<String,Object> params = (Map<String, Object>) queryObject[1];
		hql = (StringBuffer) queryObject[0];
		hql.append(" order by cp.deptId,t.ministrieID,t.classNum");
		List<ClassPlanSignUserBean> list = (List<ClassPlanSignUserBean>) executeFind(QueryStringType.HQL, hql.toString(), params,ClassPlanSignUserBean.class);
		return list;
	}
	
	/**
	 * @category 根据班级计划人数
	 * @param classId
	 * @param planUserCount
	 */
	public void updateClassUserCountById(String classId,Integer planUserCount) throws Exception{
		if(PropertyUtil.objectNotEmpty(classId)){
			String hql = "update TrainingClass t set t.classUserNum=:CLASSUSERNUM where t.classId=:CLASSID";
			Map<String, Object> paramMap = new HashMap<String, Object>();
			paramMap.put("CLASSUSERNUM", planUserCount);
			paramMap.put("CLASSID", classId);
			executeUpdate(QueryStringType.HQL, hql, paramMap );
		}
	}

	/**
	 * @category 查询班级的实际可报名额
	 * @param queryCommond
	 * @return
	 * @throws Exception 
	 */
	@SuppressWarnings("deprecation")
	public ClassPlanApplyValidCommond findClassAsignCount(ClassPlanApplyQueryCommond queryCommond) throws Exception {
		Map<String,Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select t1.asignCount as \"asignCount\",t1.asignCityCount as \"asignCityCount\"," +
				"t1.asignCountyCount as \"asignCountyCount\",t2.reportedCount as \"reportedCount\",(t1.asignCount-t2.reportedCount) as \"surplusCount\", " +
				"(select count(1) from base_user u, training_class_user cu, base_department_user du " +
				"where u.userid = cu.user_id and u.userid = du.userid and u.active_state = :activeState " +
				"and cu.class_id = :classId and du.depid = :deptId and u.administrative_level = :cityCode and cu.auditing_state <> :auditingN) as \"reportedCityCount\"," +
				"(select count(1) from base_user u, training_class_user cu, base_department_user du " +
				"where u.userid = cu.user_id and u.userid = du.userid and u.active_state = :activeState " +
				"and cu.class_id = :classId and du.depid = :deptId and u.administrative_level = :countyCode and cu.auditing_state <> :auditingN) as \"reportedCountyCount\"" +
				"from (select sum(decode(t.asign_count, null, 0, asign_count)) as asignCount," +
				"sum(decode(t.asign_city_count, null, 0, asign_city_count)) as asignCityCount," +
				"sum(decode(t.asign_county_count, null, 0, t.asign_county_count)) as asignCountyCount " +
				"from training_class_plan_apply t left join base_department d on t.dept_id = d.depid ");
		params.put("cityCode", ClassPlanApply.LEVEL_CITY);
		params.put("countyCode", ClassPlanApply.LEVEL_COUNTY);
		params.put("activeState", User.IS_ACTIVE_Y);
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreepath())) {
			sql.append(" where d.treepath like :treepath");
			sql.append(" and t.class_id = :classId) t1,(select count(cu.class_user_id) as reportedCount from training_class_user cu " +
					"where cu.class_id = :classId and cu.auditing_state <> :auditingN) t2");
			params.put("treepath", queryCommond.getSearchTreepath() + "%");
		} else if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptId())) {
			sql.append(" where d.depid= :deptId");
			sql.append(" and t.class_id = :classId) t1,(select count(cu.class_user_id) as reportedCount from training_class_user cu, " +
					"base_department_user bd where cu.user_id = bd.userid and bd.depid = :deptId and cu.class_id = :classId and cu.auditing_state <> :auditingN) t2");
			params.put("deptId", queryCommond.getSearchDeptId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			params.put("classId", queryCommond.getSearchClassId());
		}else{
			params.put("classId", "");
		}
		params.put("auditingN", ClassUser.IS_AUDITING_N);
		ClassPlanApplyValidCommond planBean = (ClassPlanApplyValidCommond) executeFind(QueryStringType.SQL, sql.toString(), params, ClassPlanApplyValidCommond.class).get(0);
		return planBean;
	}

	@SuppressWarnings("deprecation")
	@Override
	public List<ClassPlanApplyQueryBean> findClassPlanApplyQueryBeanList(
			ClassPlanApplyQueryCommond queryCommond) throws Exception {
		Map<String,Object> params = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer("select cp.PLAN_APPLY_ID as \"planApplyId\",cp.ASIGN_CITY_COUNT as \"asignCityCount\",cp.ASIGN_COUNT as \"asignCount\",cp.ASIGN_COUNTY_COUNT as \"asignCountyCount\"," +
				"cp.CITY_PLAN_COUNT as \"cityPlanCount\",cp.CLASS_ID as \"classId\", cp.COUNTY_PLAN_COUNT as \"countyPlanCount\",cp.DEPT_ID as \"deptId\",cp.DEPT_NAME as \"deptCode\",cp.PLAN_COUNT as \"planCount\"," +
				"(select count(1) from base_user u, training_class_user cu, base_department_user du " +
				"where u.userid = cu.user_id and u.userid = du.userid and u.active_state = :activeState " +
				"and cu.class_id = cp.CLASS_ID and du.depid = cp.DEPT_ID and u.administrative_level = :cityCode and cu.auditing_state <> :auditingN) as \"reportedCityCount\"," +
				"(select count(1) from base_user u, training_class_user cu, base_department_user du " +
				"where u.userid = cu.user_id and u.userid = du.userid and u.active_state = :activeState " +
				"and cu.class_id = cp.CLASS_ID and du.depid = cp.DEPT_ID and u.administrative_level = :countyCode and cu.auditing_state <> :auditingN) as \"reportedCountyCount\" " +
				"from TRAINING_CLASS_PLAN_APPLY cp left join base_department bd on cp.dept_id=bd.depid where 1=1 ");
		params.put("cityCode", ClassPlanApply.LEVEL_CITY);
		params.put("countyCode", ClassPlanApply.LEVEL_COUNTY);
		params.put("activeState", User.IS_ACTIVE_Y);
		params.put("auditingN", ClassUser.IS_AUDITING_N);
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			sql.append(" and cp.CLASS_ID = :classId");
			params.put("classId", queryCommond.getSearchClassId());
		}
		sql.append(" order by bd.order_num ");
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassPlanApplyQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		List<ClassPlanApplyQueryBean> resultList=(List<ClassPlanApplyQueryBean>) this.findList(queryCondition);
		return resultList;
	}

	/**
	 * @category 查询培训班计划名额
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-29
	 */
	public List<ClassPlanApplyQueryBean> findClassPlanApplyList(ClassPlanApplyQueryCommond queryCommond) throws Exception {
		Map<String,Object> params = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer("select decode(grouping(cp.DEPT_NAME),1,'合计',cp.DEPT_NAME) as \"deptCode\"," +
				"sum(decode(cp.CITY_PLAN_COUNT,null,0,cp.CITY_PLAN_COUNT)) as \"cityPlanCount\"," +
				"sum(decode(cp.COUNTY_PLAN_COUNT,null,0,cp.COUNTY_PLAN_COUNT)) as \"countyPlanCount\"," +
				"sum(decode(cp.PLAN_COUNT,null,0,cp.PLAN_COUNT)) as \"planCount\"," +
				"sum(decode(cp.asign_count,null,0,cp.asign_count)) as \"asignCount\"," +
				"cp.DEPT_ID as \"deptId\" " +
				"from TRAINING_CLASS_PLAN_APPLY cp left join base_department bd on cp.dept_id=bd.depid " +
				"where cp.CLASS_ID = :classId " +
				"group by rollup((cp.DEPT_NAME,cp.DEPT_ID,bd.order_num)) " +
				"order by case when \"deptCode\" = '合计' then 0 else 1 end, bd.order_num ");
		params.put("classId", queryCommond.getSearchClassId());
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(ClassPlanApplyQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		List<ClassPlanApplyQueryBean> resultList=(List<ClassPlanApplyQueryBean>) this.findList(queryCondition);
		return resultList;
	}

}
